Vlookup more than 1 column

[rhombus4]

Is it possible to use Vlookup for multiple colummns or even a range and give the value from the next column

I want to lookup data in column B, E, H and K and if found give the corresponding values in the next column.

i.e if found in E6 it will show the value in F6

 

[Swayzy]

If you need more than one value to be found that isnt driectly connected to the previous you need another instance of the function. But you can use index match or offset for example to instead of vlookup which is not the best function.

You can be Creative with sumifs, countifs but that only works for numerical values.

Generally one instance of a lookup function per cell value you need to find. Then you can either add them together or combine them as strings with&

 

[rhombus4]

I only need 1 value to be found. I enter value in A1 and it searches the columns and give the corresponding value in next column

If it finds it in H3, the value in I3 will appear in A1

 

[Special-K99]

Nest the VLOOKUPs in IFERRORs like

IFERROR(VLOOKUP(...column B...),IFERROR(VLOOKUP(...column E...)..,etc "NOT FOUND IN ANY COLUMN"))))

 

[trw1976]

Special-K99 is right, here is an example off of my sheet...

=IFERROR(VLOOKUP($C4,SAP MATERIAL SEARCH ENGINE.xlsm]Sheet1'!$E$5:$O$199000,11,FALSE),VLOOKUP($R4,SAP MATERIAL SEARCH ENGINE.xlsm]Sheet1'!$E$4:$O$199000,11,FALSE))

 

[trw1976]

I left the close bracket in there from removing my companies sharepoint link before posting this, but You get the jist.

 

[Eric W]

You could do something like this:

=IFERROR(INDIRECT(TEXT(SMALL(IF(MOD(COLUMN($B$2:$K$2),3)=2,IF($B$2:$K$10=A1,ROW($B$2:$K$10)*100+COLUMN($B$2:$K$10)+1)),1),"R00C00"),0),"Not Found")

entered with Control+Shift+Enter. But even though a 4-deep nested VLOOKUP might be longer, it would probably be more efficient.

 

[rhombus4]

I ended up using iferror and index match, but sometimes need up to 30 columns to check

 

[rhombus4]

so no way to make this easier if I have up to 100 columns to check. thought something like

=offset(vlookup(A1, B2:BK100,2,0),1,0)

 

[Eric W]

My formula from post 7 can handle multiple columns. Just change the references. You may need to adjust the MOD constant as well (red) depending on which column you start in, and if you actually have 100 or more columns, you'll need to adjust it as follows:

=IFERROR(INDIRECT(TEXT(SMALL(IF(MOD(COLUMN($B$2:$K$2),3)=2,IF($B$2:$K$10=A1,ROW($B$2:$K$10)*1000+COLUMN($B$2:$K$10)+1)),1 ),"R00C000"),0),"Not Found")