| 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
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 On Jul 26, 2:32 pm, LTaylor wrote: =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- Hide quoted text - - Show quoted text - |
|
#10
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 |
|
#11
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 - |
|
#12
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 - |
|
#13
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 - |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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 On Thu, 26 Jul 2007 06:32:06 -0700, LTaylor wrote: =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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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 On Jul 26, 4:56 pm, LTaylor wrote: 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 -- Hide quoted text - - Show quoted text - |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
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!!!!!! "LTaylor" wrote: 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 - |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup returning a value in more than one cell
Glad to hear that you got it to work - thanks for feeding back,
Pete On Jul 26, 5:42 pm, LTaylor wrote: 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!!!!!! "LTaylor" wrote: 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 -- 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 | |||
Linear Mode
