Excel file with INDEX/MATCH fix. blood_pressure-mudspike-01.zip (10.7 KB)
This fix was a strictly monkey see monkey do copy and paste affair for the most part. I do not fully understand why it is working I just know that it is working. When I used the VLOOKUP function in my spreadsheet I kept getting the #N/A error message. When I searched for a way to resolve that error I found the INDEX/MATCH solution.
Here is a link to the webpage where I found the example I used in the Excel spreadsheet I uploaded to this post.
How to correct a #N/A error in the VLOOKUP function: How to correct a #N/A error in the VLOOKUP function - Microsoft Support
Consider using INDEX/MATCH instead
INDEX and MATCH are good options for many cases in which VLOOKUP does not meet your needs. The key advantage of INDEX/MATCH is that you can look up a value in a column in any location in the lookup table. INDEX returns a value from a specified table/range—according to its position. MATCH returns the relative position of a value in a table/range. Use INDEX and MATCH together in a formula to look up a value in a table/array by specifying the relative position of the value in the table/array.
To build syntax for INDEX/MATCH, you need to use the array/reference argument from the INDEX function and nest the MATCH syntax inside of it. This take the form:
=INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])
Let’s use INDEX/MATCH to replace VLOOKUP from the example above. The syntax will look like this:
In simple English it means:
=INDEX(return a value from C2:C10, that will MATCH(Kale, which is somewhere in the B2:B10 array, in which the return value is the first value corresponding to Kale))
****(If you look at the image from their example you will see that they have placed an extra comma 0 in the formula. =INDEX(C2:C10,MATCH(B13,B2:B10,0),0) The formula works with or without that extra comma 0 but I put it in because they show it in their image example. I have no clue what it does, yet.)
The formula looks for the first value in C2:C10 that corresponds to Kale (in B7) and returns the value in C7 ( 100 ), which is the first value that matches Kale.
Here are my formulas I created from that tutorial page.
Cell D5: =INDEX(A9:A25000,MATCH(B5,G9:G25000,0),0)
=INDEX(return a value from A9:A25000, that will MATCH(Cell B5, which is somewhere in the G9:G25000 array, in which the return value is the first value corresponding to B5),0)
Cell D6: =INDEX(A9:A25000,MATCH(B6,G9:G25000,0),0)
This image shows that the formulas are returning the correct information from my spreadsheet in cells D5 and D6.
This image shows the the Highest BMI date in cell D5 is dynamic because it changed when I changed the data cell F9.
This image shows the the Highest BMI date in cell D5 and the Lowest BMI date in cell D6 are dynamic because both cells D5 and D6 changed when I changed the data in cell F9.
Now the really pleasing part of this is that I have another spreadsheet where I thought using this INDEX/MATCH solution would be the cats meow BUT when I use it in that spreadsheet I am getting the #N/A error message once again. Lol one step forward two steps back.