Is VLOOKUP/HLOOKUP The Right Tool

Originally Posted by ajax

I am trying to accomplish something and I think that HLOOKUP and/or VLOOKUP is what I need but I have not been able to get it right. I have large grid in Sheet1: (example)

Please Login or Register  to view this content.

In a seperate worksheet (sheet2) I have a listing of items where the first four characters would match the items in column 1 (A301) and the 5th and 6th characters would match up with the items in row 1(AN), and need a formula that could use that data to populate the number pulled from that combo. So that if column 1 listed an item as A304AN03, that the formula in column 2 would reflect 460 as the value. Is that possible? Am I making any sense?

Thanks in advance!
Ajax

Hi,

try

=INDEX(Sheet3!$B$2:$E$6,MATCH(LEFT(A1,4),Sheet3!$A$2:$A$6,0),MATCH(RIGHT(A1,2),Sheet3!$B$1:$E$1,0))

where A1 is A302AN etc, and sheet3 contains the table above.

hth
---