Embedding vlookup

  • Thread starter Thread starter MeanArtichoke
  • Start date Start date
M

MeanArtichoke

I'm trying to lookup a value using lookup as a reference. It seems like excel
isn't understanding to use the lookup as a value.
My current formula is returning #N/A!

=VLOOKUP((VLOOKUP(B7,Compare!A1:B50,2,TRUE)),Paste!A1:S500,10,FALSE)


Any advise would be greatly appreciated.
 
Excel works with the kind of formula you have used... So the problem is not
with your formula but the data...
First VLOOKUP will give you value from Col B of Compare and then use that to
lookup from Paste sheet. Is that what you want?

Did you check what VLOOKUP(B7,Compare!A1:B50,2,TRUE) is returning in cases
where you are getting #N/A?
Possible reasons
1. VLOOKUP(B7,Compare!A1:B50,2,TRUE) is returning #N/A because B7 is not
found in Col A of Compare sheet (data type mismatch?)

2. VLOOKUP((VLOOKUP(B7,Compare!A1:B50,2,TRUE)),Paste!A1:S500,10,FALSE)
is returning #N/A as the value returned by VLOOKUP is not there in Col A of
Paste

Replace VLOOKUP(B7,Compare!A1:B50,2,TRUE) with
ISNA(VLOOKUP(B7,Compare!A1:B50,2,TRUE),"Not
Found",VLOOKUP(B7,Compare!A1:B50,2,TRUE))
Have an entery Not Found in Col A of Paste, enter something like Formula is
Ok in 10th column (J?) in Paste and see...
 
Adding on a couple more thoughts ..

To enable easy diagnosis on what's happening, dismember it ..

Place this in say, C7:
=VLOOKUP(B7,Compare!A1:B50,2,TRUE)

Then this in D7:
=VLOOKUP(C7,Paste!A1:S500,10,FALSE)

Maybe C7 is returning text numbers (or a mix of real/text numbers) ?
You could have this in D7 to take care of both possibilities:
=VLOOKUP(C7+0,Paste!A1:S500,10,FALSE)

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
 
Back
Top