Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

index reference vlookup

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Index; Reference; VLOOKUP

Index; Reference; VLOOKUP

ResolvedVersion 2013

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.xlsx

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!

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!

Excel tip:

Deleting cells, Rows & columns

place your cursor on a cell, row number or column letter and use CTRL + -.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.