Index and Match Lookup- Formulae in lookup range

1
votes

I have two workbooks - Master work book as below

Code  Plan(KG)
2004  23
2009  67
3005  89
2003  36
4409  37
7809  25

The first column Code has formula(Ex: 2004 is extracted using formula from same sheet)

I have another sheet as below where i have to enter the plan every month. The code is fixed. But to extract plan i use INDEX and Match combination.

Code  Plan(kG)
2004
2009
3005
2003
4409
7809

The problem here is that since the lookup range has formula the plan column is returned as #NA in second column. But when i convert the master range of code from formulae to values the plan is extracted. Now i cannot change the data in master file every time.

Is there anyway around this without converting range from formulae to values?

1
excelexcel-formula
It shouldn't be such. Can you put your formula.? – Harun24HR
And the actual (or reproducible) sample data. Maybe the lookup value doesn't exist? – Robin Gertenbach
@harun24hr The formula in Master sheet is as below =IF(COUNTIF(O2N!$A:$A,TRIM(D14))>0,VLOOKUP(TRIM(D14),O2N!$A:$B,2,FALSE),TRIM(D14)) . Which means the Codes in column A of master sheet are returned by above formula – rajendra kodavaty
@RobinGertenbach The value does exist. If i convert the above formula to value, the INDEX and Match combination works for looking up the value – rajendra kodavaty

1 Answers

0
votes

=INDEX([Book5]Sheet1!$B:$B,MATCH(A2,[Book5]Sheet1!$A:$A,0)) should do.