?
Solved

Excel 2000 - Vlookup using

Posted on 2011-02-23
4
Medium Priority
?
296 Views
Last Modified: 2012-05-11
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
Comment
Question by:csehz
[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
  • Learn & ask questions
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 34959914
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
 
LVL 17

Expert Comment

by:Sikhumbuzo Ntsada
ID: 34959985
Add false at the end there that should fix your problem.
=VLOOKUP(A2;Base!A:B;2;false).
0
 
LVL 1

Author Closing Comment

by:csehz
ID: 34960006
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
 
LVL 17

Expert Comment

by:Sikhumbuzo Ntsada
ID: 34960033
@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.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question