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?

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:D,MATCH(I2,Wor king!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" wrote:
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,

Appreciate the timely help. Your solution fits the bill.

"Max" wrote:

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:D,MATCH(I2,Wor king!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" wrote:
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?

Welcome, Scott.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"Scott A" wrote in message
...
Max,
Appreciate the timely help. Your solution fits the bill.