Vlookup displays incorrect data

[Trevor3007]

hi

I have a n issue with vlookup & even though I have checked the formula. If I but the same 'value' into a few lines it does not return the correct results ( except in the 1st line) Example:-

Value (cell A1) vlookup (cell b1) vlookup (cell c1)
dog sam kennel

Value (cell A2) vlookup (cell b2) vlookup (cell c2)

dog tiddles cushion


I have not the foggiest idea why this is happening & I would appreciate greatly if someone could resolve my issue.


Kind regards
Trevor3007

 

[MrTeeny]

Hard to know what problem you're having as all you've said is it's not returning the correct results and we don't know what formula you're using or even the results it's returning, remember VLOOKUP will always find and return the first match

 

[FDibbins]

...If I but the same 'value' into a few lines it does not return the correct results ( except in the 1st line)....

And that's all it ever will return. As mentioned above, it will return the very 1st match it finds - then stop looking. If you need to return multiple matches if the same item, you will need to use something like the index/small/if ARRAY formula. But we we would need to seer some sample data for that

 

[Trevor3007]

Hi

thank you for your reply. my vlookup below:-
=IF(ISERROR(VLOOKUP(A2,pets!A2:S500,2,FALSE))," ",VLOOKUP(a2,pets!A2:S500,2,FALSE))
is there a way so I can put the same entry in several times and return the correct result?
KR
Trevor3007

 

[Trevor3007]

=IF(ISERROR(VLOOKUP(A2,pets!A2:S500,2,FALSE))," ",VLOOKUP(a2,pets!A2:S500,2,FALSE)) is what I use. How do a supply the sample data?
thank you for your reply BTW
KR
Trevor3007

 

[MrTeeny]

You keep asking for it to return the correct result but no one has a clue what result it's returning at the moment, what are you looking for and what are you actually expecting your lookup to return? Like FDibbins said if you want it to return something other than the first entry you'll have to tweak it.

[FONT=&quot]In its simplest form, the VLOOKUP function says:[/FONT]
[FONT=&quot]=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).[/FONT]

 

[Trevor3007]

You keep asking for it to return the correct result but no one has a clue what result it's returning at the moment, what are you looking for and what are you actually expecting your lookup to return? Like FDibbins said if you want it to return something other than the first entry you'll have to tweak it.

In its simplest form, the VLOOKUP function says:
=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).

thanks for your help.... I am one with why it only returns the 1 result, but you state 'you'll have to tweak it.' I don't have the knowledge to do this & politely ask if you can provide.


KR
Trevor3007

 

[AhoyNC]

If you are trying to return multiple results then you will need to use the INDEX and SMALL function.
See the attached link (you will need Excel 2010 or higher for the IFERROR function) and the example below.
Formula in E3 is an array formula that must be entered with CTRL-SHIFT-ENTER. Then drag formula down as needed.

Excel formula: Extract multiple matches into separate columns | Exceljet

Excel Workbook
	A	B	C	D	E
1	Name	Pet		Name	Mary
2	Sam	Dog		# Pets	3
3	Mary	Cat		Pets	Cat
4	Bill	Bird			Dog
5	Mary	Dog			Fish
6	Dan	Dog
7	Mary	Fish
Sheet

 

[MARK858]

(you will need Excel 2010 or higher for the IFERROR function)

I think IFERROR first arrived in Excel 2007.

IFERROR function

Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel Web App, Excel for iPhone, Excel for Android tablets, Excel Starter 2010, Excel Mobile, Excel for Android phones,

from

IFERROR function - Office Support

 

[Trevor3007]

thank you for all your help. I cannot get your 'answer' to work. Sorry am still learning 'how to'

are you able to send me a copy of the worksheet & perhaps I can then insert into mine?


Appreciate this may not be possible.

KR
Trevor3007