Johan has attended:
Excel Introduction course
Index; Reference; VLOOKUP
Hi guys, I'm trying to display data that's collected from an array list, but I don't want to display a value but a character instead.
I have the complete spreadsheet already.
I want to have a reference key like: "£" between 1-9; "££" between 10-19; "£££" more than 20
I have tried IF; VLOOKUP; INDEX but they all only return values of either TRUE or FALSE whereas I need to have a chioce of 3 values namely "£"; "££" & "£££"
Please help!
Thanks
Johan
RE: Index; Reference; VLOOKUP
Hi Johan,
Thank you for the forum question.
If I understand what you need you can use a Vlookup with a couple of nested IFS.
If you have a value in column A starting from A1 and a lookup array in the range H1 to I3 the below combination of Vlookup and nested IFS will test if the value in column A. If the value in column A is greater than 20 the lookup function will lookup up "£££" in the lookup array. If the value is greater than 9 and less than 20 it will lookup "££" in the array. For the values less than 10 it will lookup "3".
=VLOOKUP(IF(A1>19,"£££",IF(A1>9,"££","£")),$H$1:$I$3,2,0)
I hope this is what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Index; Reference; VLOOKUP
Hi Jens,
I knew I was close because I have tried referencing the "£" signs but could not get it to display the characters.
I have tried your formula but still no success. It only displays the values.
Can I email you an example spreadsheet? I can't do the original I'm afraid.
Thanks.
RE: Index; Reference; VLOOKUP
Hi Johan
Interesting question. You can use either a Vlookup or Index with Match to return the correct code.
With Vlookup
The codes (£, ££, £££) must be entered in a column to the right of the numbers (not the left)
0 £
10 ££
20 £££
Formula will be:
=VLOOKUP(B6,B1:C3,2,TRUE)
It searches for the value in B6 from B1:B3 and returns the code for the closest lower number. (TRUE part means closest lower match). So for example 19 will return ££, 20 or any larger number will return £££.
With Index and Match
No need to move the code column this time
£ 0
££ 10
£££ 20
Formula will be:
=INDEX(A1:A3,MATCH(B6,B1:B3,1))
The Index function returns the correct value from column A (£,££ or £££) based on the position of value in B6 in B1:B3 as returned by the Match function. The 1 means closest lower match (similar to TRUE for the Vlookup).
See attached file.
Use either method but number column must be sorted in ascending order and start at 0 or 1.
Regards
Doug
Best STL
Attached files...
36528_Ref_Test.xlsxRE: Index; Reference; VLOOKUP
Hi Doug,
Thanks for the reply. I ended up going down the VLOOKUP route and ended up with the same as yours:
=VLOOKUP(M12,L4:M7,2,TRUE)
I got confused when Jens recommended I incorporate some IF's.
Whilst I was working on the problem I did consider the INDEX function but gave up and decided to focus on VLOOKUP.
Thanks for providing the alternative solution as well.
Much appreciated
p.s - I did not get the attachment but not to worry - I've got the formula so that will keep me busy.
Cheers!
RE: Index; Reference; VLOOKUP
Hi Doug,
Thanks for the reply. I ended up going down the VLOOKUP route and ended up with the same as yours:
=VLOOKUP(M12,L4:M7,2,TRUE)
I got confused when Jens recommended I incorporate some IF's.
Whilst I was working on the problem I did consider the INDEX function but gave up and decided to focus on VLOOKUP.
Thanks for providing the alternative solution as well.
Much appreciated
p.s - I did not get the attachment but not to worry - I've got the formula so that will keep me busy.
Cheers!
