•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Find a lookup value, from a range of ranges, and return another cell value?
I have a list of serial numbers (about 30000). I need to find the invoice number that each searched-for serial came from, such that 54909544 falls in the range of (and matches to) B5, and returns the A5 value 73001.
The possibility exists for a serial number to have no match back to an invoiced range. In the sample below, serial 72012008 is not in any of the invoice ranges, so #N/A should be returned.
Columns are all stored as numbers. Column B values are actually via formula, e.g. =C2&":"&D2. Since C and D are each beginning and ending serials of the range, maybe it's something with nested >= and <= logic? But I'm blanking.
The cells to match against look like this:
| . | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Invoice | Range | First | Last | Qty |
| 2 | 70480 | 54766450:54766469 | 54766450 | 54766469 | 20 |
| 3 | 72232 | 54873194:54873194 | 54873194 | 54873194 | 1 |
| 4 | 72804 | 39954477:39955196 | 39954477 | 39955196 | 720 |
| 5 | 73001 | 54909542:54909545 | 54909542 | 54909545 | 4 |
| 6 | 73407 | 70389639:70389639 | 70389639 | 70389639 | 1 |
| 7 | 74621 | 72034900:72034900 | 72034900 | 72034900 | 1 |
| 8 | 74788 | 10321369:10321776 | 10321369 | 10321776 | 408 |
| 9 | 74788 | 10334597:10335256 | 10334597 | 10335256 | 660 |
| 10 | 75593 | 72035676:72035676 | 72035676 | 72035676 | 1 |
| 11 | 75593 | 72038330:72038330 | 72038330 | 72038330 | 1 |
| 12 | 75593 | 72038654:72038654 | 72038654 | 72038654 | 1 |
| 13 | 75788 | 72037205:72037206 | 72037205 | 72037206 | 2 |
| 14 | 77349 | 70399541:70399546 | 70399541 | 70399546 | 6 |
| 15 | 77506 | 21011548:21011571 | 21011548 | 21011571 | 24 |
| 16 | 79073 | 70401099:70401104 | 70401099 | 70401104 | 6 |
| 17 | 80575 | 11904634:11905113 | 11904634 | 11905113 | 480 |
| ... | ... | ... | ... | ... | ... |
| 500 | 80619 | 70407845:70407856 | 70407845 | 70407856 | 12 |
Serials snippet:
10321385 10321404 10321451 10321452 10321691 10321777 54909542 54909543 54909544 54909545 ... 72012008 [link] [comments]
Want to read more?
Check out the full article on the original site
Tagged with
#no-code spreadsheet solutions
#Excel alternatives for data analysis
#natural language processing for spreadsheets
#generative AI for data analysis
#rows.com
#AI formula generation techniques
#Excel compatibility
#financial modeling with spreadsheets
#lookup value
#serial numbers
#invoice number
#formula
#range
#match
#A5 value
#invoice ranges
#columns
#nested logic
#first serial
#last serial