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

 

[Legacy 143009]

You can use nested iferrors

=IFERROR(VLOOKUP(Sheet1!A1,C:G,5,0),IFERROR(VLOOKUP(Sheet1!A1,D:G,4,0),IFERROR(VLOOKUP(Sheet1!A1,E:G,3,0),IFERROR(VLOOKUP(Sheet1!A1,F:G,2,0)))))

 

[sandero1970]

Yes indeed but it is quite a job as in fact I have 6 columns and it is a lot of IFERROR. I was thinking may be I have to create a function in VBA in the file.
Anyway thanks for your input, it is already giving me a solution.

 

[Legacy 143009]

You don't need vba for this. One another option could be:

=INDEX(G:G,MATCH(1,(C:C=A1)+(D:D=A1)+(E:E=A1)+(F:F=A1),0))

But this is an array formula. You must press Ctrl+Shift+Enter together after paste.
Think * like AND operator and + like OR operator as in the logic.

 

[sandero1970]

It is returning me an error #N/A like he dioes not see anything
here is the adapted formula:
=INDEX(Extract_HLR!G:G,MATCH(1,(Extract_HLR!$D:$D='TB1'!$B$4)+(Extract_HLR!$E:$E='TB1'!$B$4)+(Extract_HLR!$F:$F='TB1'!$B$4)+(Extract_HLR!$G:$G='TB1'!$B$4)+(Extract_HLR!$H:$H='TB1'!$B$4),0))

 

[Legacy 143009]

(Extract_HLR!$G:$G='TB1'!$B$4)+(Extract_HLR!$H:$H='TB1'!$B$4)

Why G:G and H:H? I thought that the data you want to retrieve is already in column G? Write only the columns that you want to look up.
In INDEX function, write the only column that you want to get the data. =INDEX(Extract_HLR!G:G this looks OK.

Second thing, have you pressed Ctrl+Shift+Enter together? Click in to formula bar and pressed Ctrl+Shift+Enter together. Curly brackets must appear around your formula.

 

[Legacy 143009]

Please find the sample file for better understanding:

Sample.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[sandero1970]

Why G:G and H:H? I thought that the data you want to retrieve is already in column G? Write only the columns that you want to look up.
In INDEX function, write the only column that you want to get the data. =INDEX(Extract_HLR!G:G this looks OK.

Second thing, have you pressed Ctrl+Shift+Enter together? Click in to formula bar and pressed Ctrl+Shift+Enter together. Curly brackets must appear around your formula.

My misatke, it was an example, the data I need to have is column J so I changed but the error is still there and yes I kept ctrl shift enter

 

[sandero1970]

I guess it is a proble of data type as my data is like '0502501 but it is in general format in all sheets of the file

 

[Legacy 143009]

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