Hello, I am new here. I need some help figuring out the formula searching 2 criteria and returning the corresponding criteria from a column. On the adjusted Csg burst tab Cell H7 (Csg OD) and I7 (Nominal weight) are the 2 criteria need to be searched on the Csg Data tab. Column A (OD inch) Column B (Nominal weight) Returning the matching value from column M (wall thickness). Which using the numbers searched 7" - 23# Csg it should return 0.317 from column M. I hope I explained this well enough.
Thank you in advance
=XLOOKUP(H7&I7,'Csg Data'!A5:A838&'Csg Data'!B5:B838,'Csg Data'!M5:M838,"Not Found",0,1)
Welcome @rigman
You chose VLOOKUP so I assume you work with an older version of Excel. But VLOOKUP is not really suitable for this kind of problem. But let's first look at the reason why you get the REF error.
VLOOKUP requires the full data range with the 'key' in the first column and then you indicate the relative column number to return. In you case, the full range is A5:Q833 and you want to return M, being the 13th column in that range. Your formula wants to return the 11th column from a 1 column data range. Hence #REF!
My solution would be to use INDEX/MATCH as follows:
=INDEX('Csg Data'!A5:Q838,MATCH(H7&" "&I7,'Csg Data'!A5:A838&" "&'Csg Data'!B5:B838,0),13)
INDEX take a range, a row number and a column number.
The range to search in is 'Csg Data'!A5:Q838
The row number is found by MATCHing the concatenated values from H7 and I7, H7&" "&I7, in the concatenated ranges 'Csg Data'!A5:A838&" "&'Csg Data'!B5:B838. I've added the space between the two elements to be sure that the formula always looks for the correct combo of numbers. For instance, when you look for values 7 and 23 and leave the space out, MATCH will look for 723 rather than "7 23". If you had values 72 and 3 that would also become 723 rather than "72 3". I don't believe it's relevant with your data but in other data sets it may be. Good practice to be aware of that.
The 0 at the end of the MATCH function indicates that you want an exact match.
And finally, the column number in your case is 13.
PS: Didn't see Alan's response until after I posted mine. XLOOKUP would indeed be the preferred solution in modern Excel.
@alansidman Thank you very much, I was unable to grasp how to do what I needed done. I tried both vlookup and xlookup. My brain wasn't getting it.
@riny Thank you so very much for showing me another formula. I never even considered using index/match. Both formulas are working great from the 2 of you. I will put them to good use. I have never had any official excel training so I struggle through sometimes spending weeks trying to figure out how to make a formula work. I'm so glad I found this forum.
Thanks for the feedback. This forum is here to help. Post back any time you are having issues.