How can I change this formula so it works by searching for the first record match from the bottom most record up (rather than the top down):
=IFERROR(VLOOKUP($G6, EquipmentData!$B$3:$C$1048576, 2, FALSE),"")
How can I change this formula so it works by searching for the first record match from the bottom most record up (rather than the top down):
=IFERROR(VLOOKUP($G6, EquipmentData!$B$3:$C$1048576, 2, FALSE),"")
One way:
=LOOKUP(2,1/(EquipmentData!$B$3:$B$100=$G6),EquipmentData!$C$3:$C$100)
---------------------------
Success? Wave it, click on the little star at the bottom left of my responses
That formula came up N/A for most records. I need it to reference at least 20,000 rows on that sheet. Could it find the last row, and then work up from that?
Just modify the range to suit:
=LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)
---------------------------
Success now? Wave it, click on the little star at the bottom left of my responses
That removed all the N/As but its still referencing the higher records first. I have attached my spreadsheet. The reference sheet is EquipmentData. The last two records both have the same barcode 90909 in the B column. If you go to the ReturnData page and enter barcode 90909 into the G cell in the next empty row, the F column (which contains the formula in question) would return 2 which is the last record in the EquipmentList sheet, however its currently returning 1.
As I'm using Excel 2003, I tested it using this modification to suit the max rows in Excel 2003:
=LOOKUP(2,1/(EquipmentData!$B$3:$B$65536=$G6),EquipmentData!$C$3:$C$65536)
With 90909 in G6, the formula returned 2, which is the correct return
Perhaps you could try it again? Do ensure that calculations are set to automatic
Thank you, thats strange its working correctly now. However on blank rows (where the is currently no value in G) its displaying 0. How can I change this so it just returns blank "". I tried adding IFERROR but that didn't work.
You could use something like this (I don't have IFERROR in Excel 2003):
=IF(ISERROR(Lookup(...)),"",IF(Lookup(...)=0,"",Lookup(...)))
Note that this part: .. IF(Lookup(...)=0,"", ..
is the one which addresses your question:
> ... However on blank rows (where the is currently no value in G)
its displaying 0. How can I change this so it just returns blank "" ...
Note: I presume you meant where col C (ie the return col) was blank
As Excel returns blank cells as zeros in its calcs, that simple IF should do it for you
------------------
Success? Wave it, click on the little star at the bottom left of my responses
Last edited by Max, Singapore; 12-25-2013 at 09:23 PM.
Sorry, I'm confused as to what the full formula should be now. I tried compiling it but I keep getting errors. What should the entire complete formula be?
Are the values you are returning normally text? You can try concatenating a "" to the end of the formula to give you a blank when the reference value is blank - like this:
=LOOKUP(2,1/(EquipmentData!$B$3:$B$20000=$G6),EquipmentData!$C$3:$C$20000)&""
Audere est facere
Reply to post #9
Try this expression in say, H6:
=IF(ISERROR(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)),"",IF(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)=0,"",LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)))
Copy down
Thank you daddylonglegs and Max. They both worked, however Max that formula seemed to really slow everything down for some reason, but daddylonglegs formula is fine.
I just realized its referencing down to B1048576 which is making the code so slow. When I changed daddys code to have the same reference it to went super slow. Im guessing excel is actually checking every row. Is there some way to reference a dynamic named list, or run some formula first to find the last row (by looking for the last row containing data in C) first to prevent this issue?
Some thoughts for post#13 ...
1. From experience, I'd just go for the smallest range large enough to cover the max possible extent
2. I'd set the file's calc mode to Manual to manage performance, and recalc only when necessary
Thanks. Im probably going to have to find some alternative, as this spreadsheet will be getting very large.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks