CODEWITHSUNDEEP
excelvlookup
Amit Singh Parihar
Amit Singh Parihar

Reputation: 557

Excel: Is it possible to use Vlookup to look for a value by using 2nd or 3rd column, and return the value from the first column?

Consider a data file like this

    A       B           C       D
    8256241 524CS-18    8204    $2.96 
    8256248 14212CS-18  8204    $3.96 
    8262898 1012CS36    8204    $1.96 
     129620 500868      8029    $0.72 
     129885 502237      8029    $3.17 
     129907 50850       8029    $3.58 
     229405 6905585550  8237    $109.88 
     229413 6905585679  8237    $129.88

I want to lookup a value from column B or C, and then return a value from A. How can I do that using specifically Vlookup function?

I tried this

    VLOOKUP(500868,OFFSET('Sheet Name'!A2:D8,0,1),2,FALSE)

this looks up the value in column B instead of A, but can only return value from C or D. How can I retrieve value from A instead.?

Upvotes: 0

Views: 73

Answers (2)

findwindow
findwindow

Reputation: 3153

Looking up column B and returning column A. F1 is where I placed the value you're looking for. 

=INDEX($A$1:$A$8,MATCH(F1,$B$1:$B$8,0))

Upvotes: 4

AnalystCave.com
AnalystCave.com

Reputation: 4984

Sure it is. There are 2 options:

VLOOKUP

Check out how you can combine VLOOKUP + IF or CHOOSE to lookup on other columns.

INDEX MATCH

Link here or

INDEX - returns row (cell) from the [RETURN_COLUMN]

MATCH - returns index of [LOOKUP_VALUE] within [LOOKUP_COLUMN]

=INDEX([RETURN_COLUMN],MATCH([LOOKUP_VALUE],[LOOKUP_COLUMN],0))

Upvotes: 1

Related Questions