How to program excel with vlookup function

0
votes

I have this excel file with two sheets. Sheet 1 is the page where all of the part numbers and descriptions are placed. Sheet 2 is a database set into two columns one with part numbers the other with the description.

I have been looking into vlookup within excel and in which case i have it working with the part numbers so when i enter the part number or choose from drop down it will auto populate the description for me. The issue I am having now is that when i enter the part number it doesn't auto populate the description column until i double click the cell with the formula and hit enter.

I also want to do a reverse lookup from the description side where it will auto populate the part number as well.

Here is my formula I used for the vslookup.

=VLOOKUP(A9,Database!$A$1:$B$250,2,FALSE)

I came up with this formula after researching how to do it but I had to change the A9 portion to each row number ie. row 9, row 10, etc..

2
excel
Turn Formulas, Calculation, Calculation Options to Automatic. - user4039065

2 Answers

0
votes

If you want to do a reverse lookup then vlookup will not work as it only works left to right - unless you repeat the data in column A in column C...

However, the more elegant solution is with index() and match().

=INDEX(Database!$A$1:$A$250,MATCH(B9,Database!$B$1:$B$250,0))

Not tested, written based on the info you gave in your vlookup, match will find the position of the item looked for and then index collects the result at that position in column A.

The beauty of index/match is that the columns don't have to be next to each other AS LONG AS THEY ARE THE SAME LENGTH...

0
votes

Try right clicking the numbers column > format cells > format as text (by choosing "text" under the categories box). Hopefully this will make the auto populate work.