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

 

[Oeldere]

did you use this below (instead the normal enter):

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

 

[AhoyNC]

You can just copy and paste my example into Excel and as pointed out by Oeldere make sure you enter the formula with CTRL-SHIFT-ENTER. If you just presses enter then do the following:
Put cursor in cell with formula.
-press F2 key for edit
-Then press CTRL-SHIFT-ENTER (command -return on MAC).

 

[Trevor3007]

WOW Mark858,

You are the VB master....absolutely fantastic!!!!

you have my utter utter thanks !

KR
Trevor3007

 

[MARK858]

WOW Mark858,

I take it you meant AhoyNC and btw it is formula not VB.

 

[Trevor3007]

hi

well you also deserve a pat on the back to :}

 

[FDibbins]

The forum does not allow up/downloads

Using Ahoy's sample data...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name​

[/td][td]

Pet​

[/td][/tr]

[tr][td]

2​

[/td][td]Sam[/td][td]Dog[/td][/tr]

[tr][td]

3​

[/td][td]Mary[/td][td]Cat[/td][/tr]

[tr][td]

4​

[/td][td]Bill[/td][td]Bird[/td][/tr]

[tr][td]

5​

[/td][td]Mary[/td][td]Dog[/td][/tr]

[tr][td]

6​

[/td][td]Dan[/td][td]Dog[/td][/tr]

[tr][td]

7​

[/td][td]Mary[/td][td]Fish[/td][/tr]
[/table]

It shows that Mary has 3 pets.

Now, if you wanted to know what pets Mary has, you would need a formula like this...
[Table="width:, class:grid"][tr][td] [/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Mary[/td][/tr]

[tr][td]

2​

[/td][td][/td][td]Cat[/td][/tr]

[tr][td]

3​

[/td][td][/td][td]Dog[/td][/tr]

[tr][td]

4​

[/td][td][/td][td]Fish[/td][/tr]
[/table]
with the selected name entered into D1
D2=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$7=$D$1,ROW($A$2:$A$7)),ROWS($A$1:A1))),"")
Entered with holding down Ctrl+Shift and then pressing enter (with them still held). If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself. If it made a mistake, press F2 and try the CTRL SHIFT ENTER (CSE) again

Once you have that entered correctly, copy down as needed

To adjust that to suite your own ranges/data, adjust the bolded parts as needed
If you still have a problem, give me a shout again