[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Excel 2000 - Vlookup using

Posted on 2011-02-23
Medium Priority
298 Views
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
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
• 2

LVL 50

Accepted Solution

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)

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

cheers, teylyn
0

LVL 17

Expert Comment

ID: 34959985
=VLOOKUP(A2;Base!A:B;2;false).
0

LVL 1

Author Closing Comment

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

ID: 34960033
@teylyn Thanks for the clarification, I needed that as well.
0

## Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month14 days, left to enroll