vlookup returning a value in more than one cell

[LTaylor]

I am using the VLOOKUP formula to return a value in a cell based on a
validation list. I want to know if I pick something in the list can I have
it fill in one cell with a value from one column and another cell with a
value from another column. For example:
A B C
Program Description Products
Banking At Work description 1 online, mail, etc
Internet Banking description 2 bill pay etc

I want to pick something from the program column and have the description
fill in the next column and the products fill in the one after that.

I have Column A named Program as a range a2:a3, I have description 1 named
BankingAtWork and this works with the VLOOKUP However, I don't know how to
get column C to work with VLOOKUP based on the Programs. Thank you.

 

[Alan Beban]

=VLOOKUP("Banking At Work",a1:c3,{2,3}) array entered into the two cells
where you want the output.

Alan Beban

 

[Gord Dibben]

You need two VLOOKUP formulas in two cells.

=VLOOKUP(D2, $A$2:$C$3,2,FALSE) entered in E2

=VLOOKUP(D2, $A$2:$C$3,3,FALSE) entered in F2

DV list is in D2 where you make the selection.

I'm sure you have a greater range than A2:C3 so drag the formulas in E and F
down as far as you wish.

You will need a DV dropdown in each of D2 and D3 and whatever other cells you
want a value lookup value entered.

You may want to trap in the formulas so's you don't get #N/A

=IF(ISNA(VLOOKUP(D2, $A$2:$C$3,2,FALSE),"",VLOOKUP(D2, $A$2:$C$3,2,FALSE)


Gord Dibben MS Excel MVP

 

[Alan Beban]

You need two VLOOKUP formulas in two cells.
uh-uh.

=VLOOKUP(D2, $A$2:$C$3,2,FALSE) entered in E2

=VLOOKUP(D2, $A$2:$C$3,3,FALSE) entered in F2

You missed my post of 2:00pm? The equivalent of

=VLOOKUP(D2, $A$2:$C$3,{2,3},FALSE) array entered in E2:F2

Alan Beban

 

[LTaylor]

You are great. Thank you, thank you, thank you!!!

 

[Gord Dibben]

I don't see your 2:00pm post Alan.

Downloaded headers back to this AM but still can't see it.

Google is a mess right now so didn't search there.


Gord

 

[LTaylor]

I don't see your 2:00pm post Alan.

Downloaded headers back to this AM but still can't see it.

Google is a mess right now so didn't search there.


Gord

 

[LTaylor]

=VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE)

Above is my formula for the cell and I'm trying to make it so the #NA
doesn't show up but I must not be doing your formula correctly because I keep
getting an error. Here is what I was putting:

=IF(ISNA(VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE),"
",VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE)


Also I'm still trying to get the other issue with more than one result going
into two different cells but I'm not having any luck yet.

 

[Pete_UK]

You've missed a couple of brackets - try this:

=IF(ISNA(VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE))," ",VLOOKUP(F6,Data!$A
$93:$B$101,2,FALSE))

Hope this helps.

Pete

 

[LTaylor]

The strangest thing is happening. My formula is:
VLOOKUP(G6,Data!$B$93:$C$101,2,FALSE)

Column A Column B Column C
93 At Work description 1 Products 1
94 Youth Bank description 2 Products 2
95 Mobile ATM description 3 Products 3
96 IDA description 4
Products 4

B93 is named AtWork
B94 is named YouthBank
B95 is named MobileATM
b96 is named IDA

The cells is C are not named

When I go to my form spreadsheet if I choose "At Work" from my data
validation list in column F what I want in column G comes up and in column H.
At Work is the first on the list. Then if I pick the second or third thing
on the list, Youth Bank or Mobile ATM, it only fills in Column G and I get
#VALUE in column H. But if I pick any thing on the list after the third
item, like IDA, it works again.

I have tried everything to see if I have something in the cells C94 and C95
that are making invalid.
What am I doing wrong?

 

[Pete_UK]

I think the first parameter in your VLOOKUP should be $F6, not G6 and
your table should cover Data!$A$93:$C$101. If you make these changes
to the formula in G6 then you can copy it to H6 and just change the
3rd parameter to 3 so you have two almost identical formulae:

G6: VLOOKUP($F6,Data!$A$93:$C$101,2,FALSE)
H6: VLOOKUP($F6,Data!$A$93:$C$101,3,FALSE)

Hope this helps.

Pete

 

[LTaylor]

I have VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE) in G6 and I have

 

[LTaylor]

Sorry about that I didn't finish before it was sent

I have VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE) in G6
I have VLOOKUP(G6,Data!$B$93:$C$101,2,FALSE) in H6
Do I have to name what is in column C the same that is in Column B and if so
can that be done? Right now I don't have C93, C94 etc named anything so I
don't know how it is referencing back to column B.

Like I mentioned before it is the darnest thing that it work fines for the
first row 93 and anything after row 95 but nothing for 94 and 95. Could
there be something in those cell that is making this not work?

 

[Gord Dibben]

My bad.

See Pete's posting about the missing brackets.

I don't understand the problem with two different results going into two
different cells.

Either enter the formulas in E2 and F2 or select E2:F2 and type Alan's formula
into E2 then hit CTRL + SHIFT + ENTER.

You need 3 columns in your lookup range in order for these to work.

Example only....................

Column A has names

Column B has cities

Column C has states

Table Array would be A1:C100

Type a name into D2 and E2 will show city and F2 will show state.


Gord

 

[Pete_UK]

The value you want to match on is the pull-down that you have selected
in F6 - in column G you want to return the corresponding data from the
second column of your table and in column H you want data from the
third column of your table - in both cases matching on the data in
column A of the table. In your formula in H6, you are matching G6 with
what is in column B of your table, but you might have duplicates in
here and therefore might not match with the correct item (as VLOOKUP
picks up on the first entry which matches).

Another problem might be the size of your data table - you've got it
as A93:C101, but in your example you only quote values on rows 93 to
96. If you do not have data on all the rows in column B, then this
might explain why you get the #VALUE error.

Hope this helps.

Pete

 

[LTaylor]

I got it to work I think in my real description on the program in column B
was too long. When I shortened it the formula worked perfect. Thank you for
ALL your help!!!!!!

 

[Pete_UK]

Glad to hear that you got it to work - thanks for feeding back,

Pete