| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup returning a value in more than one cell
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
=VLOOKUP("Banking At Work",a1:c3,{2,3}) array entered into the two cells
where you want the output. Alan Beban LTaylor wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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 On Wed, 25 Jul 2007 13:46:01 -0700, LTaylor wrote: 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
Gord Dibben wrote:
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
You are great. Thank you, thank you, thank you!!!
"Alan Beban" wrote: Gord Dibben wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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 On Wed, 25 Jul 2007 18:06:02 -0700, LTaylor wrote: You are great. Thank you, thank you, thank you!!! "Alan Beban" wrote: Gord Dibben wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
"Gord Dibben" wrote: 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 On Wed, 25 Jul 2007 18:06:02 -0700, LTaylor wrote: You are great. Thank you, thank you, thank you!!! "Alan Beban" wrote: Gord Dibben wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
=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. "Gord Dibben" wrote: 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 On Wed, 25 Jul 2007 18:06:02 -0700, LTaylor wrote: You are great. Thank you, thank you, thank you!!! "Alan Beban" wrote: Gord Dibben wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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? "Alan Beban" wrote: Gord Dibben wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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 On Jul 26, 4:18 pm, LTaylor wrote: 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? "Alan Beban" wrote: Gord Dibben wrote: 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- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
I have VLOOKUP(F6,Data!$A$93:$B$101,2,FALSE) in G6 and I have
"Pete_UK" wrote: 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 On Jul 26, 4:18 pm, LTaylor wrote: 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? "Alan Beban" wrote: Gord Dibben wrote: 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- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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? "Pete_UK" wrote: 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 On Jul 26, 4:18 pm, LTaylor wrote: 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? "Alan Beban" wrote: Gord Dibben wrote: 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- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup is returning a value one cell above the correct cell. | Excel Worksheet Functions | |||
| Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA | Excel Worksheet Functions | |||
| vlookup returning #NA | Excel Discussion (Misc queries) | |||
| Vlookup returning #N/A | Excel Worksheet Functions | |||
| Vlookup and returning #n/a | Excel Worksheet Functions | |||
Hybrid Mode
