3 vlookup choice

[chef]

Hi there,
Having a brain freeze and cant get this to work and not sure where wrong although it does look untidy..

=IF($h12=$A$12,(ISNA(VLOOKUP($I12,nhs,2,0),"",VLOOKUP($I12,nhs,2,0)),=IF($H12=$A$13,(ISNA(VLOOKUP($I12,nhsb,2,0),"",VLOOKUP($I12,nhsb,2,0)),(ISNA(VLOOKUP($I12,nhsc,2,0)),"",VLOOKUP($I12,nhsc,2,0)))

I have a Month Value in Cells A12, A13 and A14

I have a drop down in column H that the user chooses a month then in column I enters an employee number.

I have 3 different worksheets ..one has a named range within that for nhs , the other nhsb and the other named range is nhsc

so what I am trying to achive is to enter the value of month in cell H12 then employee number in cell I12 then if H12 = A12 then cell I12 should lookup named range nhs,2,0....if H12 =A13 then I12 should lookup named range nhsb,2,0..otherwise value in I12 should look up named range nhsc,2,0

hope that makes sense and also need the isna as dont want NA appearing

hoping someone can make sense of this and help..

 

[rlv01]

Re: formula help please with 3 vookup choice

If what you are trying to do is something like this:

Psuedo-code:


If H12=A12 then
  do VLOOKUP using nhs
Else
  If H12=A13 then
    do VLOOKUP using nhsb
  else
    do VLOOKUP using nhsc
  end if
else 
   do VLOOKUP using nhsc
end if

Then this might work (not tested)

=IF($h12=$A$12,IF(ISNA(VLOOKUP($I12,nhs,2,0)),"",VLOOKUP($I12,nhs,2,0)),IF($H12=$A$13,IF(ISNA(VLOOKUP($I12,nhsb,2,0)),"",VLOOKUP($I12,nhsb,2,0)),IF(ISNA(VLOOKUP($I12,nhsc,2,0)),"",VLOOKUP($I12,nhsc,2,0))),IF(ISNA(VLOOKUP($I12,nhsc,2,0)),"",VLOOKUP($I12,nhsc,2,0)))

<tbody>
</tbody>

 

[chef]

Re: formula help please with 3 vookup choice

thank you so much for this
am home now and will try at work first thing in the morning

very much appreciate your help and would be great if I could get this to work
regards

 

[chef]

Re: formula help please with 3 vookup choice

Hi there
tried this and error message too many arguments when I put in your formula

Based on your formula I tried this below.
This returns the value from named range nhsc for all the months
I have April18 in A12..May18 in A13 and June18 in cell A14

So if H12 = A12 lookup I12 and named range nhs,2,0...if H12 = A13 then lookup named range nhsb,2,0 otherwise just lookup nhsc2,0

The formulua below is accepted as a format but it is picking up named range nhsc all the time and not nhs or nhsb when the months are April(A12) or May(A13) and i only getting NHSC results which is still a problem when the months chosen are different.



=IF($H12=$A$12,IF(ISNA(VLOOKUP($I12,nhs,2,0)),"",VLOOKUP($I12,nhs,2,0)),IF($H12=$A$13,IF(ISNA(VLOOKUP($I12,nhsb,2,0)),"",VLOOKUP($I12,nhsb,2,0)),VLOOKUP($I12,nhsc,2,0)))

I'm desparate to get this to work and cruical to a large development to any help at all please would be greatly appreciated as my project depends on trying to get this sorted and I cant fathom out what to do next

HELP PLEASE

 

[Aladin Akyurek]

Re: formula help please with 3 vookup choice

@ chef

Try something like:

=IFERROR(VLOOKUP($I12,CHOOSE(MATCH(H12,A12:A14,0),nhs,nhsb,nhsc),2,0),"")

 

[chef]

Re: formula help please with 3 vookup choice

Thank you Aladin

I appreciate any help I can to get this working

I have cut and paste the fornula from my spreasheet that I put in from your post...I locked A12-A14 as I need to copy formula down around 2500 rows but keep a12-a14 constant

=IFERROR(VLOOKUP($I12,CHOOSE(MATCH($H12,$A$12:$A$14,0),nhs,nhsb,nhsc),2,0),"")

when I put this is it returns no value at all?

is this something I am doing and I have checked the named ranges and should return a value..
would you be able to help me me trap where I am going wrong please.

 

[Aladin Akyurek]

Re: formula help please with 3 vookup choice

Thank you Aladin

I appreciate any help I can to get this working

I have cut and paste the fornula from my spreasheet that I put in from your post...I locked A12-A14 as I need to copy formula down around 2500 rows but keep a12-a14 constant

=IFERROR(VLOOKUP($I12,CHOOSE(MATCH($H12,$A$12:$A$14,0),nhs,nhsb,nhsc),2,0),"")

when I put this is it returns no value at all?

is this something I am doing and I have checked the named ranges and should return a value..
would you be able to help me me trap where I am going wrong please.

The MATCH bit should return either 1 or 2 or 3 or #N/A, Select the MATCH bit on the Formula Bar and hit F9.
Note what you get and hit escape. What did the MATCH bit return?

Care also post the ranges of nhs, nhsb, and nhsc?

 

[chef]

Re: formula help please with 3 vookup choice

Thank you Aladin

When I highlight match and press F9 it puts #name in the formula

named ranges as follows

nhs named range is in a worksheet called data and $A$1:$K$14415
nhsb named range is in a worksheet called datab and $A$1:$K$14415
nhsc named range is in a worksheet called datac and $A$1:$K$14415

appreciate the help

 

[chef]

Aladin

you are a genuis !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I have your formula working !!

one set of dates were dates and the other one was text

I cut/paste special the months from colums H to A12:A14 and the formula is now picking up the correct information from the different ranges.

You have noooo idea how much you have helped and was a huge bottleneck in project and could not fathom out
This is fabulous and a major step forward in a quite a big project and cannot thank you enough

kind regards
ian

 

[Aladin Akyurek]

Ian,

You are welcome. Thanks for the update.

Aladin