• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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
0
csehz
Asked:
csehz
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now