Vlookup does find the criteria when locatesd in several columns

[sandero1970]

Hello,

I have following question:

In sheet 1, I have a value in cell A1 for which I need to find a result value in Sheet 2 col G.

The complication is that my value Sheet1!A1 might be located in Sheet2 in the range C:F

A vlookup is searching value Sheet1!A1 in column C of Sheet2. So it does not work.

I've been trying with this combination but without success: =INDEX(SHEET2!G:G,EQUIV(Sheet1!A1,SHEET2!C:F,1))

Any idea how to fix this?
Thx

 

[sandero1970]

Sorry, I don't believe I can help further. Maybe you must wait for another help.

ok thx

 

[Fluff]

How about

=INDEX(Sheet2!$G:$G,AGGREGATE(15,6,ROW(Sheet2!$G$2:$G$1000)/(A1=Sheet2!$C$2:$F$1000),1))

 

[Legacy 143009]

How about

=INDEX(Sheet2!$G:$G,AGGREGATE(15,6,ROW(Sheet2!$G$2:$G$1000)/(A1=Sheet2!$C$2:$F$1000),1))

I must understand this AGGREGATE function asap

 

[sandero1970]

I must understand this AGGREGATE function asap

no this does not work at all. Thx for trying.

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.