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, 2022 in Others by Kithuzzz
• 38,010 points
1,014 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, 2022 by narikkadan
• 63,420 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, 2022 in Others by Kithuzzz
• 38,010 points
207 views
0 votes
0 answers

What is the shortcut key to delete sheet in Excel?

Could someone tell me what is the ...READ MORE

Sep 8, 2023 in Others by Satyawrat
• 460 points
174 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
512 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
• 320 points
1,015 views
0 votes
1 answer

Deleting duplicate rows in Excel using Epplus

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

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
1,033 views
0 votes
1 answer

Excel stock and sales data management

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

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
313 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, 2022 in Others by narikkadan
• 63,420 points
744 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, 2022 in Others by narikkadan
• 63,420 points
2,872 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, 2022 in Others by narikkadan
• 63,420 points
1,003 views
0 votes
1 answer

A button in excel sheet does not work for the first time

When you press the Display button, the ...READ MORE

answered Mar 30, 2023 in Others by narikkadan
• 63,420 points
222 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