Question re Vlookup function within Excel.

  oo7juk 16:34 05 Nov 07
Locked

Hi,

Can the following be done.

I have the following formula in Sheet1 of my workbook.

=VLOOKUP(B6,Sheet2!A2:BC499,5,)

When cell A2 of Sheet2 is populated with the name John and cell E2 contains 'ABC' the formula works ok, but when cell A3 is populated with the same name, but cell E3 has 'DEF' it won't display the text 'DEF'. Can this be done so that each time I enter the same name in column A with different data in column E it populates correctly.

Many thanks.

  VoG II 16:37 05 Nov 07

See 'Arbitrary Lookups' click here

  oo7juk 23:12 05 Nov 07

VoG, tried the following, but no luck.

=INDEX(Sheet2!A2:E500,SMALL(IF(Sheet2!A2:B500=B6,ROW(A2:B500)-ROW(A2)+1,ROW(B500)+1),C21),2)

Also tried -

=MATCH(B$6,Sheet2!$A$2:$E$500,0),MATCH(Sheet2!B$6,$A$2:$E$500,0)

Thanks for your assistance so far VoG.

  oo7juk 10:44 07 Nov 07

Update- Have been told that maybe vlookup is the wrong function and that excel can't do what I want it to do.

Would VBA help, for info can provide workbook for assistance.

MAny thanks.

  VoG II 17:26 07 Nov 07

I would try posting this on MrExcel if you haven't already.

  oo7juk 18:51 07 Nov 07

Thanks for advice VoG, will do.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

VFX Oscar nominees 2017: Discover how the visual effects were created

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…