Index: cross reference a row & column to return a value

Hello,

I am trying to figure out a formula that will cross reference a number across a row & column.

Please note that the example used was created to keep this as simple as possible.

I am attempting to use an index function to perform the cross reference, however I get an error in the column portion.

• Did not use concatenates because my column header has over 20 variables I need to check against
• I would hope to search across row 5 to match the "BLUE".

=INDEX(LIST,MATCH(A11,A1:A6,0),HELP)

FORUM QUESTION.PNG

Hi, welcome to the forum

Pictures often dont come through properly, and we cant really work with them anyway Please upload a sample workbook for us to play with.

Having said that, maybe this wull help a bit?
INDEX() syntax...
=index(range,row number,column)
So if you know the row and column...
=INDEX(A1:J10,2,5)
this will return the contents of teh cell and the intersection of row 2 and column E

To fone the row (or column) number, you would (often) use =MATCH()
=match(criteria-to-find,column-to-search-in,0) 0 returns exact match........for the row number
=MATCH(criteria-to-find,row-to-search-in,0)...........for teh column number
soooo that gives us...
=index(range, match(criteria-to-find,column-to-search-in,0), match(criteria-to-find,row-to-search-in,0)

Okay, I have the file uploaded now

Best way to put it is that I am trying to do something like:

Formula:

Please Login or Register  to view this content.

Ideally, like to search across row 5 which is the return value for:

Formula:

Please Login or Register  to view this content.

OK so you want to look down the # column for 13213 and look across til it finds BLUE - and then return what?

OK, but how would it know to find BLUE? What exaqctly are you trying to do here?

I ended up solving this on my business spreadsheet with a nested index match formula.