Index & Match - Match from another sheet, provide data from another column in the same row

Hatley

I have a sheet where I want to lookup/match a specific field from my active sheet [Computer Asset Tracker Lookup]@row to a 26-column-wide range {CBH HP Computer Asset & Software Tracker Range 7} in a separate source sheet. The exact/unique match would be found in column 1 in the specified range, and then I need it to return data from column 26 of the source sheet's specified range {CBH HP Computer Asset & Software Tracker Range 7} from the same @row where it found the match in column 1.

This is the initial formula I've entered:

=IFERROR((INDEX({CBH HP Computer Asset & Software Tracker Range 7}, (MATCH([Computer Asset Tracker Lookup]@row, {CBH HP Computer Asset & Software Tracker Range 7})), 26)), 0)

The result is: it finds me data from row 1 (column 26, at least) - not an accurate match thought - in the source sheet, and when I paste the formula in the next rows, it still only finds data from row 1.

The data the match is searching for should be either a 0 or unique - a serial # that will only be found once in the lookup range.

I also tried throwing in an IF statement for zeroes to stay as zeroes and not do a lookup, and though zeroes come back as zeroes, for other match attempts, it still comes back to finding data from row 1:

=IFERROR(IF([Computer Asset Tracker Lookup]@row = 0, 0, (INDEX({CBH HP Computer Asset & Software Tracker Range 7}, (MATCH([Computer Asset Tracker Lookup]@row, {CBH HP Computer Asset & Software Tracker Range 7})), 26)), 0))

Taking the IFERROR off, I've tried adding a ", 0" after the match to tell it to look for an exact match, and that comes back with errors - #INVALID VALUE

=IF([Computer Asset Tracker Lookup]@row = 0, 0, (INDEX({CBH HP Computer Asset & Software Tracker Range 7}, (MATCH([Computer Asset Tracker Lookup]@row, {CBH HP Computer Asset & Software Tracker Range 7}, 0)), 26)))

Genevieve P.

Hi @Hatley

It sounds like you may have the column order the wrong way around. If Column 1 is the one you're searching for values in, then this should be the second range, listed within the MATCH function.

=INDEX({CBH HP Computer Asset & Software Tracker COLUMN 26}, MATCH([Computer Asset Tracker Lookup]@row, {CBH HP Computer Asset & Software Tracker COLUMN 1}, 0))

The MATCH is what tells the formula what row to look in - if finds the Match in Column 1 and then is says "this row number" within the First column listed (column 26).

Let me know if I can clarify this further!

Cheers,

Genevieve

Kelly Moore

Hello @Hatley

I believe you are mixing VLOOKUPs and Index/Match syntaxes together. The VLOOKUP requires a table structure. The Index/Match does not require this tabular format - you can specify specific columns as ranges and pull directly from those specific columns. It is only calling to 2 columns that allows the Index/Match to have better sheet performance than many VLOOKUPs.

The syntax on INDEX/Match is

=INDEX(Range that has the data you're trying to find, MATCH([data to match to]@row, range you're matching to, 0))

=INDEX({CBH HP Computer Asset & Software Tracker COLUMN 1}, MATCH([Computer Asset Tracker Lookup]@row, {CBH HP Computer Asset & Software Tracker COLUMN 26}, 0))

You'll have to recreate the cross sheet references. Each range is the single column - not the table from column 1 to column 26. As a good practice, you can rename the ranges prior to clicking the INSERT REFERENCE button so that the range name reflects the actual column name you are referencing.

Does that work for you?

Kelly

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

Hatley

Didn't work. When I tried limiting the 1st search range to column 1 and the 2nd search range to column 26, it comes back as #No Match. It definitely needs to somehow understand we're matching from column 1 in a search array to column 26 in the same array, that way it knows where to look.

Index/Match definitely ask for the row index and column index in the 26-column-wide search array, and the part where it comes back to column 26 does work, but the part that isn't working is the matching to search for a specific row and pull the data from that row and column 26.

Genevieve P.

Hi @Hatley

It sounds like you may have the column order the wrong way around. If Column 1 is the one you're searching for values in, then this should be the second range, listed within the MATCH function.

=INDEX({CBH HP Computer Asset & Software Tracker COLUMN 26}, MATCH([Computer Asset Tracker Lookup]@row, {CBH HP Computer Asset & Software Tracker COLUMN 1}, 0))

The MATCH is what tells the formula what row to look in - if finds the Match in Column 1 and then is says "this row number" within the First column listed (column 26).

Let me know if I can clarify this further!

Cheers,

Genevieve

Kelly Moore

oops,my bad. Thanks for the catch @Genevieve P.

Hatley

That did the trick! Thank you both!