Embedding vlookup

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

 

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

 

[Don Guillett]

Have you tried without the nesting to see if working.

 

[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

 

[Ashish Mathur]

Hi,

Instead of VLOOKUP(B7,Compare!A1:B50,2,TRUE), try this
VLOOKUP(B7,Compare!A1:B50,2,FALSE).

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com