Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP first numerical value (not text)
Dear all, I have this situation: RATENAME | DAY1 | DAY2 | DAY3 | DAY4 myrate | 200 | 200 | | | myrate | | | 250 | 250 | ... and so on in a separte worksheet I would like to have: RATENAME | DAY1 | DAY2 | DAY3 | DAY4 myrate | 200 | 200 | 250 | 250 | .... and so on so basically compressing the list in the first worksheet to one row in second worksheet. My problem is that vlookup only looks up the first result it finds, even if the field is empty (see screenshots). I would instead need it to find the first numerical value, as I am only itnerested in this. This is the vlookup I am currently using: =IF(ISERROR(VLOOKUP($A5,EFI!$B$3:$GK$1500,23,FALSE )),0,VLOOKUP($A5,EFI!$B$3:$GK$1500,23,FALSE)) Thank you for any help you can give me.. Titus. +-------------------------------------------------------------------+ |Filename: excel-problem2.jpg | |Download: http://www.excelforum.com/attachment.php?postid=2996 | +-------------------------------------------------------------------+ -- titushanke ------------------------------------------------------------------------ titushanke's Profile: http://www.excelforum.com/member.php...o&userid=19997 View this thread: http://www.excelforum.com/showthread...hreadid=345841 |
#2
|
|||
|
|||
hi there, u can use the subtotal function at each change in "myrate" - end result u get a subtotal for all the coloumns. now click on all the minus sign on the left of the sheet. now u get onlyu the sub totals. now use cntrl+g click on special . n select visible cells now. now paste it another sheet.. n replace the word total with null.. -- m_anoob ------------------------------------------------------------------------ m_anoob's Profile: http://www.excelforum.com/member.php...o&userid=19892 View this thread: http://www.excelforum.com/showthread...hreadid=345841 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions | |||
Command: If the contents (text not numerical) of A1 in Sheet2 are. | Excel Worksheet Functions | |||
How can I get text to equal a numerical value in Excel? | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
VLookup accesses half the text in a field? | Excel Worksheet Functions |