Using INDEX & MATCH to search different columns

[Scott A]

Hi. I am trying to develop a worksheet that searches someone else's XLS to
reconcile data. The other user has data in two differnt columns (to keep
prints to one page and save space).

I am using this formula =INDEX(Working!B:B,MATCH(I2,Working!A:A,0)) to
look for data and match my data to their data for our reports. For example,
if a computer is inventoried in office # 1 and gets moved to office #3, my
report should reflect office #3 as well. However, the computer might be in
column C or it might be listed in column F.

Is there a way to use this formula (or some other) to query different
columns in the same sheet and return to me the location?

 

[Max]

You can nest it to sequentially index n match, something like this ...

Assuming that in sheet: Working
the lookup/match values are listed in cols A and C,
with corresponding return values in cols B and D
(col B for col A, col D for col C)

On your sheet, the lookup values are in col I, in I2 down
You could use this in say, H2
=IF(ISNA(MATCH(I2,Working!A:A,0)),IF(ISNA(MATCH(I2,Working!C:C,0)),"",INDEX(Working!D,MATCH(I2,Working!C:C,0))),INDEX(Working!B:B,MATCH(I2,Working!A:A,0)))
and copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Scott A]

Max,

Appreciate the timely help. Your solution fits the bill.

 

[Max]

Welcome, Scott.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik