HLOOKUP, VLOOKUP, INDEX or MATCH

[Rhothgar]

Hi
I'm stuck on a spreadsheet on which I have managed to get VLOOKUP to work on one range. I'm made up that I've managed to get that to work but it's one basic piece of the jigsaw.
I cannot get xl2bb to work either so cannot snapshot the spreadsheet.
I want to be able to enter a weight in cell Q3 (currrently shown as 0.25kg in Q3 but might be 0.87 or 1.35, etc) and it check the range G3:K3 say by looking at the range in G2:K2 and then return either 8.64 for 0.25kg, 11.74 for 0.87kg or 13.74 for 1.35kg.
I want to be able to copy it into each row below Row 3 where 0.25 is currently entered and it come up with the correct result for the data in each row. So if I type 0.75 in Row 8, it will return 10.09 in Column R.
I think this makes sense. If unclear, please ask.

Thanks in advance.

PS Managed to get correct figure for insurance working as per Insurance Values screenshot using =VLOOKUP($K$35,$H$38:$I$117, 2, FALSE) in cell K38.
Insurance Table.jpg is actually base price of postage and not insurance for clarity.

 

[Rhothgar]

Well. I have managed to get it to work but I do not think I am using the best solution.
In cell R3 for instance, I have =HLOOKUP(Q3,$G$2:$K$29,2,TRUE)
in cell R4, I have =HLOOKUP(Q3,$G$2:$K$29,3,TRUE)
in cell R5, I have =HLOOKUP(Q3,$G$2:$K$29,4,TRUE)

and so on.
So the row_index_num had to be manually altered on every line as fill down didn't work.
If I needed to move the equation in Column R to another location, it clearly doesn't work because the row behave differently to a VLOOKUP it seems ie a column is a column.

I can work with it for now but there must be a better solution.

 

[Rhothgar]

Just come to the realisation that there is an issue with this method.
If I enter 0.36 in cell Q3, cell R3 reports 8.64 when it should select the 8.94 band because 0.36 is between 0.25 and 0.5.
I cannot work out how to do this, can some clever bod please advise?
TIA

 

[Alex Blakenburg]

See if this gives you any ideas:
Note:
If you want the next largest item if the value is inbetween you might need a column with a higher value to catch everything exceeding 2.
Its worth getting XL2BB working, post a thread on what is not working if you need to. You will generally get a better response rate if you provide and XL2BB.

20230213 XLookup or HLookup Rhothgar.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	0.25	0.5	0.75	1	2						Weight	Prince
3	8.64	8.94	10.64	11.74	13.74						0.36	8.94
4	8.04	9.64	10.04	10.34	11.94						0.25	8.04
5	9.79	12.09	14.49	16.79	99						1.5	99
6
Sheet1

Cell Formulas
Range	Formula
R3:R5	R3	=XLOOKUP(Q3,$G$2:$K$2,$G3:$K3,"",1)

 

[Rhothgar]

=XLOOKUP(Q3,$G$2:$K$2,$G3:$K3,"",1)

WOW! That is absolutely incredible.
I nearly mentioned XLOOKUP but never investigated it as I had enough on my plate trying to understand HLOOKUP and VLOOKUP using Microsoft's Help facility.
I could really do with trying to expand my understanding of Excel.
I used to be quite good with it or so I thought. That was back in the early 1990's though and it's not something I used every day.

 

[Rhothgar]

Do you know of an idiot's guide that is any good? One where the explanations are simple to understand and potentially graphic like this...

 

[Rhothgar]

And one further question if I may? What does the " " represent in your code please?
Is it the if_not_found argument (if that's the right word)?
The Microsoft site doesn't seem to make any reference to what " " means in this function.
EDIT! I've found it. It simply means it returns TEXT written within it but in this case it is invalid unless I go over the 2kg mark. I say invalid but I can now write something in there should I think it is relevant but it is clearly very good practice to maintain syntax and I am guessing that is EXACTLY why you included it.
Thank you ever so much. You've made my day!

 

[Alex Blakenburg]

Haha glad to hear it.
Typically you don't put a space between the quotes and it is referred to as an empty string, there is no option for a formula to return an actual blank cell.
When you use VLookup or HLookup and a value is Not Found they return #N/A, to avoid that the VLookup or HLookup can be wrapped in an IFERROR.
eg =IFERROR(Vlookp_Function, ""). So the if the VLookup returns #N/A it will return what what comes after the comma, commonly this is an empty string but it can be Text, a Number or even another function such as an alternative lookup.
XLookup has this functionality built into the function.

 

[Rhothgar]

Haha glad to hear it.
Typically you don't put a space between the quotes and it is referred to as an empty string, there is no option for a formula to return an actual blank cell.
When you use VLookup or HLookup and a value is Not Found they return #N/A, to avoid that the VLookup or HLookup can be wrapped in an IFERROR.
eg =IFERROR(Vlookp_Function, ""). So the if the VLookup returns #N/A it will return what what comes after the comma, commonly this is an empty string but it can be Text, a Number or even another function such as an alternative lookup.
XLookup has this functionality built into the function.

My only issue now is some following cells, if I enter a figure greater than 2 give an #N/A because they rely on the reported data in this cell.
Finally got XL2BB reinstalled and working. Just need to read up how to use it...