Embedding vlookup

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.
 
S

Sheeloo

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...
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top