21 05 03.xlsm | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | W | X | Y | Z | ||||||||||||||||||||
1 | post #3 | post #2 | ||||||||||||||||||||||||||
2 | jones, alan | 1 | 4 | 7 | smith | 9 | 8 | |||||||||||||||||||||
3 | smithson, anne | 2 | 5 | 8 | ||||||||||||||||||||||||
4 | smith, david | 3 | 6 | 9 | ||||||||||||||||||||||||
VLOOKUP |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Y2 | Y2 | =VLOOKUP(X2&",*",A2:D100,4,0) |
Z2 | Z2 | =VLOOKUP(X2&"*",A2:D100,4,0) |
Thanks although I couldnt get this to work. May because this is the otherway round ie looking up eg smith within a lookup range of smith,peterI would suggest including the comma in the lookup value (Y2 formula) to avoid a possible incorrect result (Z2 formula)
21 05 03.xlsm
A B C D E W X Y Z 1 post #3 post #2 2 jones, alan 1 4 7 smith 9 8 3 smithson, anne 2 5 8 4 smith, david 3 6 9 VLOOKUP
Cell Formulas Range Formula Y2 Y2 =VLOOKUP(X2&",*",A2:D100,4,0) Z2 Z2 =VLOOKUP(X2&"*",A2:D100,4,0)
Name | Gross pf new | Fnights | Total | |
Baker, Annie | $1,000.00 | 4.00 | =SUM(C5*D5)+VLOOKUP(A5&"*",C9:E11,2,FALSE) | |
Baker | Blue | 2237 | ||
Bawden | Red | 2273 | ||
Smith | Green | 2283 |
Not sure but couldnt get this to workYou can do that likewhere X2 is the cell with the value to find.Excel Formula:=VLOOKUP(X2&"*",A2:D100,4,0)
Name | Gross pf new | Fnights | Total | |
Baker, Annie | $1,000.00 | 4.00 | =SUM(C5*D5)+VLOOKUP(A5&"*",C9:E11,2,FALSE) | |
Baker | Blue | 2237 | ||
Bawden | Red | 2273 | ||
Smith | Green | 2283 |
Yes, it appears that you described this the wrong way around in your original post. But it also looks like you have the wrong column referenced in the lookup range in your latest attempt.May because this is the otherway round
21 05 03.xlsm | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
4 | Name | Gross pf new | Fnights | Total | |||
5 | Baker, Annie | $1,000.00 | 4 | $6,237.00 | |||
6 | |||||||
7 | |||||||
8 | |||||||
9 | Baker | Blue | 2237 | ||||
10 | Bawden | Red | 2273 | ||||
11 | Smith | Green | 2283 | ||||
VLOOKUP (2) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5 | E5 | =C5*D5+VLOOKUP(LEFT(A5,FIND(",",A5)-1),C9:E11,3,FALSE) |
thanks so much..worked a treatYes, it appears that you described this the wrong way around in your original post. But it also looks like you have the wrong column referenced in the lookup range in your latest attempt.
Is below what you want?
BTW, I suggest that you
- Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
- Investigate XL2BB for providing sample data to make it easier for helpers.
21 05 03.xlsm
A B C D E 4 Name Gross pf new Fnights Total 5 Baker, Annie $1,000.00 4 $6,237.00 6 7 8 9 Baker Blue 2237 10 Bawden Red 2273 11 Smith Green 2283 VLOOKUP (2)
Cell Formulas Range Formula E5 E5 =C5*D5+VLOOKUP(LEFT(A5,FIND(",",A5)-1),C9:E11,3,FALSE)