Look Up Column & Row

[usc1382]

I can not seem to find the right steps to look up a row and a column in Excel. For example, I am trying to look up a Column of unique Note Numbers with a row of specific attributes to a another like database in excel. Please see the attachment for a very easy understanding of the issue.

[NBVC]

Do you mean, in L5:

=INDEX(C$5:C$12,MATCH(1,INDEX(($B$5:$B$12=$K5)*($E$5:$E$12=$N5),0),0))

copied to next column

If match of both items in same row not found, you get #N/A error.

[usc1382]

Thanks so much for your help. I apologize as I might of explained the issue wrong. I have updated the excel attachment and below is what I am trying to accomplish.

I want to take my New Database and look up the Retail # 20 and the corresponding School in my Original Database and produce a result. In this case the End Result was Bob after Excel looked up the Retail Number of 20 and the corresponding Name. I am just trying to take it a step farther versus using a Vlookup and aligning it was index_num and would like to look up the row & column

[NBVC]

In L5, then, try:

=VLOOKUP($K5,$B$5:$E$12,COLUMNS($A$1:B$1),0)

copied across the matrix.

[Blake 7]

Dear NVBC - or anyone who may be able to answer........

In an attempt to learn and develop my skills I often come onto this site and DL practice/read q and a's.

On this occassion - i dont understand the last section of this formula.. =VLOOKUP($K5,$B$5:$E$12,COLUMNS($A$1:B$1),0) which is $A$1:B$1),0).

On the example provided, the cells a1 and b1 are blank........ am i missing something?

Thanks
Darren

[NBVC]

COLUMNS($A$1:B$1) simply results in a number.. the number of columns within the range Identified.

This is a technique used to replace the Index Column Number hard coding, so that you can copy the formula across and have it automatically increase the column index number for the vlookup automatically.

So COLUMNS($A$1:B$1) results in 2 (column 2)

when you copy across this over to next columns it becomes COLUMNS($A$1:C$1) which results in 3 so it will now look in 3rd column.. and so on.

[Blake 7]

i tested it and have answered my own q!! the cols, in this case a and b, donthave to be empty! Thanks for your time
whoa, boy,, am i gonna use that!! i needed that last week and last month!!

In fact, this is a nougat!

In hommage to Columbo... one more Q, same subject, if you dont mind. Does the first Col always have to be empty as in this case? (Col A)

Thanks Dude. Have you been to Red Bay - Canada?

[NBVC]

the Columns() function only counts the number of columns between the specified range. It does not matter if they are empty or not... It has nothing to do with the data in the columns.

See the Column and Columns functions (Also Row and Rows) in Excel help for more.

[Blake 7]

Thanks alot.
All the best
Darren