Hi guys,
I am using a VLOOKUP against data from a cell which is part of a dropdown (n/a, yes or no).
My Vlookup is returning '0' - can anyone advise why this is?
This is my formula: =VLOOKUP('Snapshot - Full Year'!A3, 'Q1 2018_summary'!A1:BL312, 52, FALSE)
I am attaching an image of the cells I am trying to reference from
Thanks,
R.
Can we see the sheet, rather than a non-editable picture of the sheet? Check that A3 and the target are both TEXT or both NUMBERS, but not a mixture.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
I deleted a lot of data to see if I can upload the sheet and the formula works - so it is pointless uploading it as it is without the data and it just doesn't make sense. The text is all set as GENERAL.
Any other suggestions?
Maybe you can try an Index-Match:
=INDEX("column 52", MATCH('Snapshot - Full Year'!A3,'Q1 2018_summary'!$A$1:$A$312,0))
"column 52" - the column that contains the results to be returned.
A problem, I agree! However, without seeing what is REALLY in the source and target cells, we could be in for a lengthy guessing game.
Hi PaulM100
Thank you for your suggestion.
This is returning #VALUE! in all the cells unfortunately
maybe you can do a =istext or =isnumber to check on your lookup value first to confirm is it in text or number.
Last edited by finalazy; 03-15-2018 at 05:52 AM.
if you know that what you are looking for in A3 is actually in 'Q1 2018_summary'!A but it is returning N/A then use =A3='Q1 2018_summary'!A3 (or wherever the values should match - in other words, find two that (should) match) and if it returns false then you know the values in the two locations are not the same. Maybe there is a space in one that isn't in the other.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
how do I do this? thank you!
I am simplying looking to link the data so against A3, it needs to pull data in from the other tab (the data would be: N/A, NO or YES). I don't want to use the = method to link the data, incase the data is changed i.e the order of content in A3. Hence why I want to use a VLOOKUP
thanks!
find a value you know is in both lists, say the first is in cell A3 in the Snapshot - Full Year tab.
Let's say that you know this value appears in cell A27 of the Q1 2018_summary tab.
In an empty cell put =A3=Q1 2018_summaryA27 and if it returns true they match, if it returns false then they may look like they match but they don't.
If you are getting 0 returned, without an IFERROR around your VLOOKUP formula, this implies that the cell to be returned is actually blank and Excel returns a zero instead. You can overcome this by doing this:
=VLOOKUP('Snapshot - Full Year'!A3, 'Q1 2018_summary'!A1:BL312, 52, FALSE)&""
Hope this helps.
Pete
See attached an example.
If you delete one of those entries in column AZ of Sheet 2 (so that it is blank), you will see a zero in the corresponding place on Sheet1. You can change the formula in B1 to this:
=INDEX(Sheet2!$AZ:$AZ,MATCH(A1,Sheet2!A:A,0))&""
to avoid this happening.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks