Lookup with Data NOT Sorted

  • Thread starter Thread starter mcasillas
  • Start date Start date
M

mcasillas

Is there any way to use a lookup function which allows the following:
lookup data across different workbooks
but my data cannot be sorted

Scenario:
Workbook 1

Cell A1 - Lookup(IV1, workbook 2 cell:IV1, workbook 2 cell D1)
Cell IV1- contains lookup data (unique identifier)

Workbook 2

Cell IV1- contains lookup data (unique identifier)
Cell D1- contains data i want to bring back to workbook 1

The formula currently returns 0, which indicates to me I've don
something wrong.
I'm working on Excel version 2002.

Things that MIGHT be causing problem/things I've checked: lookup data
some rows have missing data
lookup data- range begins with empty data, but even when i populate
returns 0.
Verified lookupdata in workbook 1 matches exactly in workbook 2
verfied format of data
verified IV1 is being evaluated correctl
 
Unfortunately, I am not able to move the columns around. It looks lik
Vlookup's lookup value must be in the leftmost column.

Thanks for your help. I appreciate your time
 
Hi
this is true but you may use INDEX,MATCH as a workaround. e.g.
=INDEX($A$1:$A$100,MATCH(X1,$B$1:$B$100,0))

to look in column b but return the corresponding value from column A
 
Hi.
Really? I did not know.

Could you write how that would look? I'm not quite sure how.
Do I nest the functions together?
Which one goes first?

Thanks again.
You have no idea what a life saver you are
 
Hi
just replace VLOOKUP with this kind of INDEX / MATCH functions. e.g.
the formula
=VLOOKUP(X1,A1:C100,3,0)
could be replaced by
=INDEX(C1:C100,MATCH(X1,A1:A100,0))
 
Back
Top