Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Broken vlookup

Posted on 2013-01-25
Medium Priority
350 Views
Hi,

I need to map two values through a vlookup formula but it is not working. Can you please refer to the attached file and correct the existing formula?

thanks
Broken-Vlookup.xlsx
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2
• 2
• +2

LVL 13

Expert Comment

ID: 38818839
``````=IF(\$B3=\$A3,IFERROR(VLOOKUP(\$A3,B!\$A\$3:\$D\$91,2,FALSE),"STORE NOT FOUND!"),"STORE & LC DOES NOT MATCH")
``````

Try this

Also

VLOOKUP(\$A3,B!\$A\$3:\$D\$91,2,FALSE)

Change the "2" as you wish
0

Author Comment

ID: 38818917
no this does not work...I have to have the existing format b/c it is part of a bigger file. so in the attached file you can see in tab A Cell C3 "false". but if you go to tab B you'll see \$\$ for store 90 which should equal store 42 in tab A. Does it make sense? Please refer to the attached. thanks!
Broken-Vlookup.xlsx
0

LVL 50

Expert Comment

ID: 38818970
Why are you checking if A3 = B3? In this case A3 = 90 and B3 = 42 so the VLOOKUP isn't actioned - you just get FALSE because A3<>B3

This VLOOKUP will give the required value

=VLOOKUP(\$A3,B!\$A\$3:\$B\$91,2,FALSE)

it looks up A3 in the first column of the range B!\$A\$3:\$B\$91 and returns the value from the column specified (2)

If you want to prevent errors with that try

=IFERROR(VLOOKUP(\$A3,B!\$A\$3:\$B\$91,2,FALSE),"No match")

regards, barry
0

LVL 11

Expert Comment

ID: 38818986
The condition as per your formula is always going to be false as the two values you mentioned in column A and B in Worksheet A  are not same hence the answer will always be false,

if you Just wish to get any other value from sheet B based on value in a particular cell then just use the Vlookup formula as

=Vlookup(\$AX,B!\$A\$3:\$D\$91,XX,False)

Where AX is the cell reference
and XX is the Column number in sheet B

for e.g.  XX=1 for Store # column value
XX=2 for Net Sales Home Comp Column value...and so on
0

Author Comment

ID: 38819040
"Why are you checking if A3 = B3? In this case A3 = 90 and B3 = 42 " these are store numbers and they are the same in my other report. that's why by saying that A3=B3 i need to pull the \$\$ for store 90 in tab B but it will actually be considered as \$\$ for store 42 in Tab A....if it is not a vlookup formula please suggest another one. So for store 59 in Tab A I need to pull \$\$ from tAB B for store 114, for store 66 TAB A I need to have \$\$ data that comes from tab B for store 173 and so on.... once I collect them they will be added to a different report.
0

LVL 50

Expert Comment

ID: 38819059
Did you try just the VLOOKUP on its own as I suggested - i.e.

=VLOOKUP(\$A3,B!\$A\$3:\$B\$91,2,FALSE)

Note: that's also the same formula that Shanan212 suggested

regards, barry
0

LVL 24

Accepted Solution

Steve earned 1200 total points
ID: 38819239
Using INDEX and MATCH to acomplish the same result.

This can be more versatile than VLOOKUP.
Broken-Vlookup.xlsx
0

LVL 11

Expert Comment

ID: 38819303
Look at the attached sheet
Broken-Vlookup.xlsx
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll