Combination of IF & Vlookup, what will be the syntax ?

[Jyotirmaya]

In my sheet I want that IF H5 of Backend sheet will be matched with some text of D5 then it will search in a range of Data sheet.

I am trying for code that

=VLOOKUP(H5,IF(D5="CAR", ???? then it should search a range from the DATA sheet for the H5 if the D5 matches with text "CAR". what should be the code ??

 

[Aladin Akyurek]

What is the value of H5?
What is the range wherein to look for H5 when D5 = CAR?
What must happen when D5 does not equal CAR?

 

[clovisjim]

Are you trying to do something like either of these...

=IFERROR(vlookup(H5,A1:A50,1,0),"")

=IF(ISERROR(vlookup(H5,A1:A50,1,0)),vlookup(H5,A1:D50,3,0),"")

This would just validate that the text you are searching for exists in the the lookup table and then return a blank. If this isn't helpful, I would need more information from you like the previous poster stated.

 

[clovisjim]

First option should have been...

=IFERROR(vlookup(H5,A1:D50,3,0),"")

 

[Jyotirmaya]

What is the value of H5?
What is the range wherein to look for H5 when D5 = CAR?
What must happen when D5 does not equal CAR?

The value of H5 is not certain, it will change but the value of H5 is there in the DATA sheet. the value of H5 is there in B column of Data sheet. I want the associated vertical data of D column of DATA sheet. so the normal Vlookup will be like this =VLOOKUP(H5,DATA!A1:F50,4,FALSE) but I want to add the If function, if the D5="CAR" then it will search a different range, then if D5=BIKE then it will search a different range & so on. if D5 not equal to CAR then result may be come zero.

 

[Jyotirmaya]

First option should have been...

=IFERROR(vlookup(H5,A1:D50,3,0),"")

How can I add the function IF D5 ="CAR" then...... in the above code ?

 

[clovisjim]

Maybe try something like this...

=IF(ISERROR(FIND("CAR",A6,1)),VLOOKUP(A6,H11:I13,1,0),VLOOKUP(A6,H6:I8,2,0))

I had to make up some dummy data so you would have to play with the actual syntax.

 

[clovisjim]

Instead of FIND, use SEARCH...

=IF(ISERROR(SEARCH("CAR",A6,1)),VLOOKUP(A6,H11:I13,1,0),VLOOKUP(A6,H6:I8,2,0))

 

[Jyotirmaya]

Instead of FIND, use SEARCH...

=IF(ISERROR(SEARCH("CAR",A6,1)),VLOOKUP(A6,H11:I13,1,0),VLOOKUP(A6,H6:I8,2,0))

I am getting #N/A with the above syntax

 

[clovisjim]

Without knowing how your spreadsheet is setup, I don't know what is causing the issue. Do you have more detail?