Excel VLOOKUP where the key is not in the first column

0 votes
"the location of the lookup value in the range. Keep in mind that for VLOOKUP to function properly, the lookup value must always be in the range's first column. For instance, if cell C2 has the lookup value, your range should begin with C."

However, there are moments when I wish I could perform dual-direction lookups. I.e., use a key in column A to lookup the value in column B AND, at the same time, lookup the value in column B to get the value in column A in other calculations.

The only way I'm aware of is to include a column C that exactly duplicates column A, then use AB for the first lookup and BC for the second lookup. But there must be a better option. Is there a method to compel VLOOKUP to look up the key value in a column other than the initial one, or is there another function that can do the same thing?
Oct 20 in Others by Kithuzzz
• 20,660 points
53 views

1 answer to this question.

0 votes

INDEX/MATCH will do it in any direction of search.

So for your example of B --> A:

=INDEX(A:A,MATCH(yourCriteria,B:B,0))

The MATCH returns the matching row's number. It's optional to use the third Criterion of 0. When it comes to the fourth VLOOKUP criterion, which searches for an exact match, the value 0 is equivalent to FALSE. The match that is less than or equal to the criteria, like VLOOKUP's TRUE, will be returned if the data is sorted and the default value is 1.

The INDEX determines the right value from that and returns it.

answered Oct 20 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2 in Others by Kithuzzz
• 20,660 points
25 views
0 votes
1 answer

Where is the documentation to refer for coinbase api integration of Etherium coin currency in php?

Hey there! Please refer to the following ...READ MORE

answered Jan 25, 2019 in Others by Omkar
• 69,210 points
319 views
+4 votes
0 answers

What is the best way to pass CISSP Exam In first attempt?

Is CISSP Certification Worth. And What is ...READ MORE

Jun 25, 2019 in Others by Eric
• 310 points
680 views
0 votes
1 answer
0 votes
1 answer

Deleting duplicate rows in Excel using Epplus

You need to re-think this… the while ...READ MORE

answered Feb 23 in Database by gaurav
• 22,040 points
296 views
0 votes
1 answer

Excel stock and sales data management

you must attach the event handler each ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
45 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
65 views
0 votes
1 answer

Way to overcome Excel Vlookup function limit of 256 characters

If you are using VLOOKUP like this: =VLOOKUP(A2,D2:Z10,3,FALSE) i.e. ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
141 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11 in Others by narikkadan
• 37,660 points
36 views
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9 in Others by narikkadan
• 37,660 points
191 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP