"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 972 views

1 answer to this question.

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
• 63,420 points

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

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

What is the shortcut key to delete sheet in Excel?

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

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

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

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

Deleting duplicate rows in Excel using Epplus

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

Excel stock and sales data management

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

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

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