Excel 2000 - Vlookup using

Dear Experts,

Could you please have look the attached file, it simply contains a table on "Base" sheet

Item      Qty
A      10
B      5
C      9

from where the values are vlookup-ed on Sheet1 with formula =VLOOKUP(A2;Base!A:B;2;0).

Do you have maybe idea how to use VLOOKUP with a kind of condition on Item - if the Item is B, it should not bring the value so 5, in that case should bring value C so 9. With such trick the result on Sheet1 would be like below

Item      Qty
A      10
B      9
C      9


In my work unfortunately has such need, sometimes I have do summary sheets with vlookup, and there are such examples that if the value is "RedAuto", it is also auto so should bring value "Auto"

thanks,
AlternativeVlookup.xls
LVL 1
csehzIT consultantAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

this description is not sufficient to provide a solution.

"if the Item is B, it should not bring the value so 5, in that case should bring value C so 9."

I have a feeling that this is a little too much simplification.  The formula would be

=VLOOKUP(IF(A2="B","C",A2),Base!A:B,2,1)

copied down. And that answers the question you have asked.

But I'm not convinced that this is the real solution you are after.

cheers, teylyn
0
 
Sikhumbuzo NtsadaSenior IT TechnicianCommented:
Add false at the end there that should fix your problem.
=VLOOKUP(A2;Base!A:B;2;false).
0
 
csehzIT consultantAuthor Commented:
Teylyn thanks, yes I also need to think on it how will use in live, but your solution works for me.

Santasi24, I tried your formula but that one still brings 5 for B value, I would wait there 9.
0
 
Sikhumbuzo NtsadaSenior IT TechnicianCommented:
@teylyn Thanks for the clarification, I needed that as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.