backward vlookup?

[fozo]

Hello everyone,

I am trying to do a backward vlookup and I need your help on how to write one. The return data on the source table is the first column, and my look up data on the source table is 4th column. How am I going to write up a formula so that on my target table it will return the data with the 4th column on the source table?

Thanks much.

 

[ghrain22]

Index match:
How to Use INDEX MATCH

 

[fozo]

I tried this =INDEX('July 2014'!B:E,MATCH('Gas Cards'!E,'July 2014'!B:E,0),4) and got #NAME? error.

July 2014 is my source table
Column B:E is my source data

Gas Cards is my target table
E is my look up data on target table

So, I want to use column E on target table and look up column B:E in my source table and give me the result of column B.

Please help.

 

[ghrain22]

Oh man entire columns. That's going to destroy your file size and calculation speed.

Regardless you got an error because 'Gas Cards'!E is not a valid reference. However you'd probably want that to be a single cell reference instead of a range(for that parameter).

try this and copy paste it down the values on your gas cards sheet(assuming your the data you're looking at starts at E1):
index('July 2014'!$B:$E,Match(E1,'July 2014'!E:E,0),1)

index(RangeYou'reLookingIn, Match(WhatYou'reLookingFor,WhereToFindIt), ColumnYouWantToReturn)

Column you want to return is 1 because in the range B:E the first column would be B:B

 

[fozo]

Not sure what I did wrong but I still got error.

Here is my formular: =INDEX('July 2014'!$B:$E,MATCH(F2,'July 2014'!E5,0),1)

'July 2014'!$B:$E is my source table - July 2014 or Sheet 1

F2 is single data on my target table - Gas Cards or Sheet 2

What will be the formula on Gas Card or Sheet 2 that using F2 data to match with column E in Sheet 1 and give me match data of Column B?

 

[ghrain22]

The second parameter on your match should be a single column range not a single cell reference.

I'm confused on how your data is layed out then.
Where is the formula being placed?
Where is your lookup value?
What range are you looking for it in?

I don't know where sheet1 and sheet2 came into play.

 

[fozo]

Sorry for the confussion. Here are my data

Sheet 1:
Column B contains my Asset #
Column E contains my VIN #

Sheet 2:
Column F contains my VIN #
Column K = formula

Trying to get Sheet 1/Column B on to Sheet 2/Column K.

 

[ghrain22]

=INDEX('Sheet1'!$B:$E,MATCH(F2,'Sheet1'!E:E,0),1)

put that in f2 and copy it down

 

[fozo]

That's it. Thanks so much.