Forum Home
Press F1
Thread ID: 61264	2005-08-30 06:22:00	excel formula	bpt2 (6653)	Press F1

Post ID	Timestamp	Content	User
1357232	2005-08-30 06:22:00	I have a data table of values B2:f45 and want to return a value from f2:f45 where the same row in column b2:b45 matches a cell in another worksheet.	bpt2 (6653)
1357233	2005-08-30 06:32:00	I should add that the cell in the other worksheet may contain text, e.g. G11	bpt2 (6653)
1357234	2005-08-30 06:35:00	Use an If statement to do the comparison, just go into the functions and you should see how it works. =If(blah = blah,do this, else do this) See if you can use that to your advantage. Cheers, KK	Kame (312)
1357235	2005-08-30 06:52:00	I've tried IF(F4=SHEET1!B2:B53,SHEET1!F2:F53,0) where cell F4 = "G11" but just get "0" and there is a cell in SHEET1!B2:B53 which = "G11"	bpt2 (6653)
1357236	2005-08-30 09:27:00	Hi, Im not sure I understand but I think you want to look down column B and find the row that matches cell f4, then if it does, move along the row until you hit column F then return that value. If f4 wasnt found in column B then return 0. If this is the case then try... =IF(ISNA(VLOOKUP(F4,Sheet1!B2:F53,5,FALSE)),0,VLOO KUP(F4,Sheet1!B2:F53,5,FALSE))	Parry (5696)
1357237	2005-08-31 03:39:00	Yes, you're right, but your formula does not work either. I have been trying the formula =VLOOKUP(f4,Sheet!B2:F53,3), but it does not work consistently. f4 is always found in column b of the sheet1, so there is no need for the ISNA part. The formula appears to work fine when f4 is one of the numbers 1-10 (found in column b of the sheet1, but when f4 = "g11", also found in column b of the sheet1, it does not work.	bpt2 (6653)
1357238	2005-08-31 04:20:00	Hi, theres nothing wrong with the formula but will be something to do with the target data format/value not matching the source data. For example, if your wanting to find the number 12 it will find it if its a true number 12 but not if its a text 12 (eg "12"). Similarly if text has a trailing space eg ("HELLO ") then vlookup will not locate it with the word "HELLO" because the values need to be exact. I could look at it for you if you want. Just send me a PM and I will give you my email address to send to.	Parry (5696)
1357239	2005-08-31 06:00:00	Yes, you're right, but your formula does not work either. I have been trying the formula =VLOOKUP(f4,Sheet!B2:F53,3), but it does not work consistently. f4 is always found in column b of the sheet1, so there is no need for the ISNA part. The formula appears to work fine when f4 is one of the numbers 1-10 (found in column b of the sheet1, but when f4 = "g11", also found in column b of the sheet1, it does not work. Not wishing to be picky but the formula you quote above =VLOOKUP(f4,Sheet!B2:F53,3) will not do as you want (maybe it's just a typo) As Parry has said the correct formula is =VLOOKUP(F4,Sheet1!B2:F53,5,FALSE) The ,5, finds data in column F (when starting at column B). If you are copying this formula, you need to make the data range absolute before copying it ie Sheet1!$B$2:$F$53. Otherwise it works fine for text g11 in cell f4 with text g11 in column B and finds any data in column f across from cell b containing text g11.	rad_s4 (7401)
1357240	2005-08-31 06:19:00	If you are copying this formula, you need to make the data range absolute before copying it ie Sheet1!$B$2:$F$53. Otherwise it works fine for text g11 in cell f4 with text g11 in column B and finds any data in column f across from cell b containing text g11. Good point! Thats probably the issue - the formula was copied down and pointing to the wrong range. Makes sense if you can find the first few then not from then on.	Parry (5696)
1357241	2005-08-31 06:40:00	Thanks, making the array values absolute seemed to make the difference. I did have: =VLOOKUP(F4,Sheet1!B$2:$F$53,5,FALSE), but when I changed it to =VLOOKUP(F4,Sheet1!$B$2:$F$53,5,FALSE) it all worked out. Not quite sure why?	bpt2 (6653)
1